using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Web.Core;
using Web.Core.Objects;

namespace Web.Core.Providers
{
    public class SqlProvider : DataProvider
    {
        string connectionString = GlobalStorage.connectionString;

        public string ConnectionString
        {
            get { return connectionString; }
            set { connectionString = value; }
        }

        protected SqlConnection GetSqlConnection()
        {
            try
            {
                return new SqlConnection(ConnectionString);
            }
            catch
            {
                throw new Exception("SQL Connection String is invalid.");
            }
        }


        #region Products Table
        public override ArrayList GetProductSpecial(int top, int language, bool special)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                //SqlCommand cmd = new SqlCommand("products_special", connection);
                //cmd.CommandType = CommandType.StoredProcedure;
                //cmd.Parameters.Add(new SqlParameter("@num",top));
                //cmd.Parameters.Add(new SqlParameter("@language", language));
                //cmd.Parameters.Add(new SqlParameter("@special", special == true ? 1 : 0));
                //ArrayList arr = new ArrayList();
                //connection.Open();
                //using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                //{
                //    while (reader.Read())
                //    {
                //        arr.Insert(arr.Count, PopulateProductFromIDataReader(reader));
                //    }
                //    reader.Close();
                //    connection.Close();
                //}
                return null;
            }
        }

        public override ArrayList GetProductBit(int num, int language, bool special, bool promotion, bool newest)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand cmd = new SqlCommand("products_selectbit", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@num", num));
                cmd.Parameters.Add(new SqlParameter("@language", language));
                cmd.Parameters.Add(new SqlParameter("@special", SqlDbType.Bit)).Value = special;
                cmd.Parameters.Add(new SqlParameter("@promotion", SqlDbType.Bit)).Value = promotion;
                cmd.Parameters.Add(new SqlParameter("@newest", SqlDbType.Bit)).Value = newest;
                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, PopulateProductFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        public override ArrayList GetProducts(GlobalStorage.TypeProducts type, int category, GlobalStorage.Show visible, int language, string orderBy, int pageNumber, int pageSize, GlobalStorage.Order order, int pID, float pPrice, int pManufacturer, string pName, string pDes)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Products_Select", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@type", (int)type));
                command.Parameters.Add(new SqlParameter("@category", category));
                command.Parameters.Add(new SqlParameter("@visible", (int)visible));
                command.Parameters.Add(new SqlParameter("@language", language));
                command.Parameters.Add(new SqlParameter("@orderBy", orderBy));
                command.Parameters.Add(new SqlParameter("@pageNumber", pageNumber));
                command.Parameters.Add(new SqlParameter("@pageSize", pageSize));
                command.Parameters.Add(new SqlParameter("@order", (int)order));
                command.Parameters.Add(new SqlParameter("@pId", pID));
                command.Parameters.Add(new SqlParameter("@pPrice", pPrice));
                command.Parameters.Add(new SqlParameter("@pManufacturer", pManufacturer));
                command.Parameters.Add(new SqlParameter("@pName", Utils.SqlInjectionProtect(pName)));
                command.Parameters.Add(new SqlParameter("@pDes", Utils.SqlInjectionProtect(pDes)));
                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, PopulateProductFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        public static Product PopulateProductFromIDataReader(IDataReader dr)
        {
            Product prt = new Product();
            prt.ProductID = ConvertType.ToInt(dr["product_id"]);
            //prt.ManufacturerID = ConvertType.ToInt(dr["manufacturer_id"]);
            prt.CategoryID = ConvertType.ToInt(dr["category_id"]);
            prt.NumOrdered = ConvertType.ToInt(dr["product_ordered"]);
            prt.NumInStock = ConvertType.ToInt(dr["product_in_stock"]);
            prt.Image = Utils.SqlInjectionDeprotect(dr["product_image"].ToString());
            prt.DateAdded = ConvertType.ToDate(dr["date_added"]);
            System.Web.SessionState.HttpSessionState session = System.Web.HttpContext.Current.Session;
            prt.Price = ConvertType.ToDouble(dr["product_price"]);
            if (session["Currency"] != null)
                prt.Price = prt.Price * (double)(session["Currency"] as Currency).Rate;
            prt.Visible = ConvertType.ToBool(dr["product_visible"]);
            prt.Special = ConvertType.ToBool(dr["product_special"]);
            prt.Name = Utils.SqlInjectionDeprotect(dr["product_name"].ToString());
            prt.Description = Utils.SqlInjectionDeprotect(dr["product_description"].ToString());
            prt.Top = ConvertType.ToBool(dr["product_top"]);
            prt.Promotion = ConvertType.ToBool(dr["product_promotion"]);
            return prt;
        }

        public override int CountProducts(GlobalStorage.TypeProducts type, int category, GlobalStorage.Show visible, int language, float pPrice, int pManufacturer, string pName, string pDes)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Products_Count", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@type", (int)type));
                command.Parameters.Add(new SqlParameter("@category", category));
                command.Parameters.Add(new SqlParameter("@visible", (int)visible));
                command.Parameters.Add(new SqlParameter("@language", language));
                command.Parameters.Add(new SqlParameter("@pPrice", pPrice));
                command.Parameters.Add(new SqlParameter("@pManufacturer", pManufacturer));
                command.Parameters.Add(new SqlParameter("@pName", Utils.SqlInjectionProtect(pName)));
                command.Parameters.Add(new SqlParameter("@pDes", Utils.SqlInjectionProtect(pDes)));

                int count = 0;
                connection.Open();
                count = (int)command.ExecuteScalar();
                connection.Close();

                return count;
            }
        }

        public override ArrayList GetBestSellers(int lang)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Products_BestSellers", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@num", GlobalStorage.bestSellersNum));
                command.Parameters.Add(new SqlParameter("@language", lang));
                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, PopulateProductFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        public override int CreateUpdateDeleteProducts(Product pro, ShopContent content, GlobalStorage.Action choice)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Products_InsertDeleteUpdate", connection);
                command.CommandType = CommandType.StoredProcedure;
                int productID = -1;
                if (choice == GlobalStorage.Action.Insert)
                    command.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
                else
                    command.Parameters.Add(new SqlParameter("@id", pro.ProductID));

                command.Parameters.Add(new SqlParameter("@nameEn", Utils.SqlInjectionProtect(content.NameEn)));
                command.Parameters.Add(new SqlParameter("@nameVi", Utils.SqlInjectionProtect(content.NameVi)));
                command.Parameters.Add(new SqlParameter("@desEn", Utils.SqlInjectionProtect(content.DesEn)));
                command.Parameters.Add(new SqlParameter("@desVi", Utils.SqlInjectionProtect(content.DesVi)));
                command.Parameters.Add(new SqlParameter("@image", Utils.SqlInjectionProtect(pro.Image)));
                command.Parameters.Add(new SqlParameter("@price", pro.Price));
                command.Parameters.Add(new SqlParameter("@dateAdded", pro.DateAdded));
                //command.Parameters.Add(new SqlParameter("@manID", pro.ManufacturerID));
                command.Parameters.Add(new SqlParameter("@catID", pro.CategoryID));
                command.Parameters.Add(new SqlParameter("@ordered", pro.NumOrdered));
                command.Parameters.Add(new SqlParameter("@instock", pro.NumInStock));
                command.Parameters.Add(new SqlParameter("@visible", pro.Visible));
                command.Parameters.Add(new SqlParameter("@special", pro.Special));
                command.Parameters.Add(new SqlParameter("@promotion", pro.Promotion));
                command.Parameters.Add(new SqlParameter("@choice", (int)choice));

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                if (choice == GlobalStorage.Action.Insert)
                    productID = (int)command.Parameters["@id"].Value;
                else
                    productID = pro.ProductID;

                return productID;
            }
        }

        #endregion


        #region Categories Table
        public override int CreateUpdateDeleteCategories(Category cat, ShopContent content, GlobalStorage.Action choice)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Categories_InsertDeleteUpdate", connection);
                command.CommandType = CommandType.StoredProcedure;
                int categoryID = -1;
                if (choice == GlobalStorage.Action.Insert)
                    command.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
                else
                    command.Parameters.Add(new SqlParameter("@id", cat.Id));

                command.Parameters.Add(new SqlParameter("@nameEn", Utils.SqlInjectionProtect(content.NameEn)));
                command.Parameters.Add(new SqlParameter("@nameVi", Utils.SqlInjectionProtect(content.NameVi)));
                command.Parameters.Add(new SqlParameter("@desEn", Utils.SqlInjectionProtect(content.DesEn)));
                command.Parameters.Add(new SqlParameter("@desVi", Utils.SqlInjectionProtect(content.DesVi)));
                command.Parameters.Add(new SqlParameter("@image", Utils.SqlInjectionProtect(cat.Image)));
                command.Parameters.Add(new SqlParameter("@sequence", cat.Sequence));
                command.Parameters.Add(new SqlParameter("@choice", (int)choice));
                command.Parameters.Add(new SqlParameter("@parent", cat.Parent));
                command.Parameters.Add(new SqlParameter("@top", cat.Top));
                command.Parameters.Add(new SqlParameter("@man", cat.Manufact));

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                if (choice == GlobalStorage.Action.Insert)
                    categoryID = (int)command.Parameters["@id"].Value;
                else
                    categoryID = cat.Id;

                return categoryID;
            }
        }
        public override ArrayList GetAllCategories(int language, int pageNumber, int pageSize)
        {
            ArrayList kq = new ArrayList();
            ArrayList cats = GetCategories(0, "", "", 0, language, 1);
            int count = cats.Count;
            int start = (pageNumber - 1) * pageSize;
            int end = start;
            if (start < 0)
                return null;
            else
            {
                if (start + pageSize - 1 >= count)//het
                    end = count - 1;
                else
                    end = start + pageSize - 1;
                for (int i = start; i <= end; i++)
                {
                    kq.Add(cats[i]);
                }
            }
            return kq;
        }

        public override ArrayList GetCategories(int catID, string catName, string catDes, int catSequence, int lang, int parrent)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Categories_Select", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@catID", catID));
                command.Parameters.Add(new SqlParameter("@catName", Utils.SqlInjectionProtect(catName)));
                command.Parameters.Add(new SqlParameter("@catDes", Utils.SqlInjectionProtect(catDes)));
                command.Parameters.Add(new SqlParameter("@catSequence", catSequence));
                command.Parameters.Add(new SqlParameter("@language", lang));
                command.Parameters.Add(new SqlParameter("@parrent", parrent));
                ArrayList arr = new ArrayList();
                SqlDataReader reader;
                connection.Open();
                using (reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, CategoryFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        public override ArrayList GetAllChild(int parentID, int lang)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Categories_GetChild", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@parentid", parentID));
                command.Parameters.Add(new SqlParameter("@language", lang));
                ArrayList arr = new ArrayList();
                SqlDataReader reader;
                connection.Open();
                using (reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, CategoryFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        public override int CountCategories(string catName, string catDes, int catSequence, int lang)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Categories_Count", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@catName", Utils.SqlInjectionProtect(catName)));
                command.Parameters.Add(new SqlParameter("@catDes", Utils.SqlInjectionProtect(catDes)));
                command.Parameters.Add(new SqlParameter("@catSequence", catSequence));
                command.Parameters.Add(new SqlParameter("@language", lang));

                int count = 0;
                connection.Open();
                count = (int)command.ExecuteScalar();
                connection.Close();

                return count;
            }
        }

        public override ArrayList GetCategoriesByQuantity(int quantity, int lang)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Categories_SelectNum", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@quantity", quantity));
                command.Parameters.Add(new SqlParameter("@language", lang));
                ArrayList arr = new ArrayList();
                SqlDataReader reader;
                connection.Open();
                using (reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, CategoryFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        public Category CategoryFromIDataReader(SqlDataReader reader)
        {
            Category cat = new Category();
            cat.Id = int.Parse((reader["category_id"]).ToString());
            cat.Name = Utils.SqlInjectionDeprotect((reader["category_name"]).ToString());
            cat.Description = Utils.SqlInjectionDeprotect((reader["category_description"]).ToString());
            cat.Image = Utils.SqlInjectionDeprotect((reader["category_image"]).ToString());
            cat.Parent = int.Parse((reader["parent_id"]).ToString());
            cat.Top = ConvertType.ToBool(reader["category_top"]);
            cat.Manufact = int.Parse((reader["category_manufact"]).ToString());
            return cat;
        }

        public override ArrayList GetTop(int lang)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Categories_GetTop", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@language", lang));
                ArrayList arr = new ArrayList();
                SqlDataReader reader;
                connection.Open();
                using (reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, CategoryFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        public override ArrayList GetAllCategoriesByManufact(int manu, int lang)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Categories_GetAllByManu", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@manu", manu));
                command.Parameters.Add(new SqlParameter("@language", lang));
                ArrayList arr = new ArrayList();
                SqlDataReader reader;
                connection.Open();
                using (reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, CategoryFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }
        #endregion


        #region Manufacturers Table
        public override ArrayList GetAllManufacturers(int pageNumber, int pageSize)
        {
            ArrayList kq = new ArrayList();
            ArrayList mans = GetManufacturers(0, "", "");
            int count = mans.Count;
            int start = (pageNumber - 1) * pageSize;
            int end = start;
            if (start < 0)
                return null;
            else
            {
                if (start + pageSize - 1 >= count)//het
                    end = count - 1;
                else
                    end = start + pageSize - 1;
                for (int i = start; i <= end; i++)
                {
                    kq.Add(mans[i]);
                }
            }
            return kq;
        }
        public override int CreateUpdateDeleteManufacturers(Manufacturer man, GlobalStorage.Action choice)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Manufacturers_InsertDeleteUpdate", connection);
                command.CommandType = CommandType.StoredProcedure;
                int manufacturerID = -1;
                if (choice == GlobalStorage.Action.Insert)
                    command.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
                else
                    command.Parameters.Add(new SqlParameter("@id", man.MID));

                command.Parameters.Add(new SqlParameter("@name", Utils.SqlInjectionProtect(man.MName)));
                command.Parameters.Add(new SqlParameter("@url", Utils.SqlInjectionProtect(man.MURL)));
                command.Parameters.Add(new SqlParameter("@choice", (int)choice));

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                if (choice == GlobalStorage.Action.Insert)
                    manufacturerID = (int)command.Parameters["@id"].Value;
                else
                    manufacturerID = man.MID;

                return manufacturerID;
            }
        }

        public override ArrayList GetManufacturers(int mID, string mName, string mURL)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Manufacturers_Select", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@mID", mID));
                command.Parameters.Add(new SqlParameter("@mName", Utils.SqlInjectionProtect(mName)));
                command.Parameters.Add(new SqlParameter("@mURL", Utils.SqlInjectionProtect(mURL)));
                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, PopulateManufacturerFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        private Manufacturer PopulateManufacturerFromIDataReader(IDataReader dr)
        {
            Manufacturer man = new Manufacturer();
            man.MID = ConvertType.ToInt(dr["manufacturer_id"]);
            man.MName = Utils.SqlInjectionDeprotect(dr["manufacturer_name"].ToString());
            man.MURL = Utils.SqlInjectionDeprotect(dr["manufacturer_url"].ToString());
            return man;
        }

        #endregion


        #region Advertisements Table
        public override int CreateUpdateDeleteAdvertisements(Advertisement ads, GlobalStorage.Action choice)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Advertisements_InsertDeleteUpdate", connection);
                command.CommandType = CommandType.StoredProcedure;
                int advertisementID = -1;
                if (choice == GlobalStorage.Action.Insert)
                    command.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
                else
                    command.Parameters.Add(new SqlParameter("@id", ads.ID));

                command.Parameters.Add(new SqlParameter("@image", Utils.SqlInjectionProtect(ads.Image)));
                command.Parameters.Add(new SqlParameter("@url", Utils.SqlInjectionProtect(ads.Url)));
                command.Parameters.Add(new SqlParameter("@choice", (int)choice));

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                if (choice == GlobalStorage.Action.Insert)
                    advertisementID = (int)command.Parameters["@id"].Value;
                else
                    advertisementID = ads.ID;

                return advertisementID;
            }
        }
        public override ArrayList GetAllAdvertisements(int pageNumber, int pageSize)
        {
            ArrayList kq = new ArrayList();
            ArrayList ads = GetAdvertisements(new Advertisement(0, "", ""));
            int count = ads.Count;
            int start = (pageNumber - 1) * pageSize;
            int end = start;
            if (start < 0)
                return null;
            else
            {
                if (start + pageSize - 1 >= count)//het
                    end = count - 1;
                else
                    end = start + pageSize - 1;
                for (int i = start; i <= end; i++)
                {
                    kq.Add(ads[i]);
                }
            }
            return kq;
        }
        public override ArrayList GetAdvertisements(Advertisement ads)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Advertisements_Select", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@ads_id", ads.ID));
                command.Parameters.Add(new SqlParameter("@ads_image", Utils.SqlInjectionProtect(ads.Image)));
                command.Parameters.Add(new SqlParameter("@ads_url", Utils.SqlInjectionProtect(ads.Url)));

                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, PopulateAdsFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        public static Advertisement PopulateAdsFromIDataReader(IDataReader dr)
        {
            Advertisement ads = new Advertisement();
            ads.ID = ConvertType.ToInt(dr["ads_id"]);
            ads.Image = Utils.SqlInjectionDeprotect(dr["ads_image"].ToString());
            ads.Url = Utils.SqlInjectionDeprotect(dr["ads_url"].ToString());
            return ads;
        }
        #endregion


        #region Currencies Table
        public override int CreateUpdateDeleteCurrencies(Currency cur, GlobalStorage.Action choice)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Currencies_InsertDeleteUpdate", connection);
                command.CommandType = CommandType.StoredProcedure;
                int currencyID = -1;
                if (choice == GlobalStorage.Action.Insert)
                    command.Parameters.Add("@cID", SqlDbType.Int).Direction = ParameterDirection.Output;
                else
                    command.Parameters.Add(new SqlParameter("@cID", cur.ID));
                command.Parameters.Add(new SqlParameter("@cName", cur.Name));
                command.Parameters.Add(new SqlParameter("@cSymbol", cur.Symbol));
                command.Parameters.Add(new SqlParameter("@cRate", cur.Rate));
                command.Parameters.Add(new SqlParameter("@choice", (int)choice));

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                if (choice == GlobalStorage.Action.Insert)
                    currencyID = (int)command.Parameters["@cID"].Value;
                else
                    currencyID = cur.ID;

                return currencyID;
            }
        }
        public override ArrayList GetCurrencies(int cID, string cName, string cSymbol, float cRate)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Currencies_Select", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@cID", cID));
                command.Parameters.Add(new SqlParameter("@cName", Utils.SqlInjectionProtect(cName)));
                command.Parameters.Add(new SqlParameter("@cSymbol", Utils.SqlInjectionProtect(cSymbol)));
                command.Parameters.Add(new SqlParameter("@cRate", cRate));
                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, PopulateCurrencyFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        private object PopulateCurrencyFromIDataReader(IDataReader dr)
        {
            Currency curr = new Currency();
            curr.ID = ConvertType.ToInt(dr["currency_id"]);
            curr.Name = Utils.SqlInjectionDeprotect(dr["currency_name"].ToString());
            curr.Symbol = Utils.SqlInjectionDeprotect(dr["currency_symbol"].ToString());
            curr.Rate = ConvertType.ToFloat(dr["currency_rate"]);
            return curr;
        }
        #endregion


        #region Languages Table
        public override ArrayList GetLanguages(int lID, string lName)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Languages_Select", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@lID", lID));
                command.Parameters.Add(new SqlParameter("@lName", Utils.SqlInjectionProtect(lName)));
                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, PopulateLanguageFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        private object PopulateLanguageFromIDataReader(IDataReader dr)
        {
            Language lang = new Language();
            lang.ID = ConvertType.ToInt(dr["language_id"]);
            lang.Name = Utils.SqlInjectionDeprotect(dr["language_name"].ToString());
            return lang;
        }
        #endregion


        #region ContactUs Table
        public override int InsertDeleteUpdateContact(Contact c, GlobalStorage.Action choice)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("ContactUs_InsertDeleteUpdate", connection);
                command.CommandType = CommandType.StoredProcedure;
                int contactID = -1;
                if (choice == GlobalStorage.Action.Insert)
                    command.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
                else
                    command.Parameters.Add(new SqlParameter("@id", c.ID));

                command.Parameters.Add(new SqlParameter("@name", Utils.SqlInjectionProtect(c.Name)));
                command.Parameters.Add(new SqlParameter("@phone", Utils.SqlInjectionProtect(c.Title)));
                command.Parameters.Add(new SqlParameter("@email", Utils.SqlInjectionProtect(c.Email)));
                command.Parameters.Add(new SqlParameter("@message", Utils.SqlInjectionProtect(c.Message)));
                command.Parameters.Add(new SqlParameter("@date_added", c.DateAdded));
                command.Parameters.Add(new SqlParameter("@choice", (int)choice));

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                if (choice == GlobalStorage.Action.Insert)
                    contactID = (int)command.Parameters["@id"].Value;
                else
                    contactID = c.ID;

                return contactID;
            }
        }

        public override ArrayList GetContact(Contact c, int pageNumber, int pageSize)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("ContactUs_Select", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@id", c.ID));
                command.Parameters.Add(new SqlParameter("@name", Utils.SqlInjectionProtect(c.Name)));
                command.Parameters.Add(new SqlParameter("@phone", Utils.SqlInjectionProtect(c.Title)));
                command.Parameters.Add(new SqlParameter("@email", Utils.SqlInjectionProtect(c.Email)));
                command.Parameters.Add(new SqlParameter("@message", Utils.SqlInjectionProtect(c.Message)));
                command.Parameters.Add(new SqlParameter("@date_added", c.DateAdded));
                command.Parameters.Add(new SqlParameter("@pageNumber", pageNumber));
                command.Parameters.Add(new SqlParameter("@pageSize", pageSize));

                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, PopulateContactFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        public static Contact PopulateContactFromIDataReader(IDataReader dr)
        {
            Contact con = new Contact();
            con.ID = ConvertType.ToInt(dr["contactus_id"]);
            con.Name = Utils.SqlInjectionDeprotect(dr["contactus_name"].ToString());
            con.Title = Utils.SqlInjectionDeprotect(dr["contactus_title"].ToString());
            con.Email = Utils.SqlInjectionDeprotect(dr["contactus_email"].ToString());
            con.Message = Utils.SqlInjectionDeprotect(dr["contactus_message"].ToString());
            con.DateAdded = ConvertType.ToDate(dr["date_added"]);

            return con;
        }

        public override int CountContact(Contact c)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("ContactUs_Count", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@id", c.ID));
                command.Parameters.Add(new SqlParameter("@name", Utils.SqlInjectionProtect(c.Name)));
                command.Parameters.Add(new SqlParameter("@phone", Utils.SqlInjectionProtect(c.Title)));
                command.Parameters.Add(new SqlParameter("@email", Utils.SqlInjectionProtect(c.Email)));
                command.Parameters.Add(new SqlParameter("@message", Utils.SqlInjectionProtect(c.Message)));
                command.Parameters.Add(new SqlParameter("@date_added", c.DateAdded));

                int count = 0;
                connection.Open();
                count = (int)command.ExecuteScalar();
                connection.Close();

                return count;
            }
        }
        #endregion


        #region Members Table
        public override int InsertDeleteUpdateMember(Member mem, GlobalStorage.Action choice)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Members_InsertDeleteUpdate", connection);
                command.CommandType = CommandType.StoredProcedure;
                int memID = -1;
                if (choice == GlobalStorage.Action.Insert)
                    command.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
                else
                    command.Parameters.Add(new SqlParameter("@id", mem.ID));

                command.Parameters.Add(new SqlParameter("@email", Utils.SqlInjectionProtect(mem.Email)));
                command.Parameters.Add(new SqlParameter("@password", mem.Password));
                command.Parameters.Add(new SqlParameter("@sex", (int)mem.Sex));
                command.Parameters.Add(new SqlParameter("@firstName", Utils.SqlInjectionProtect(mem.FirstName)));
                command.Parameters.Add(new SqlParameter("@lastName", Utils.SqlInjectionProtect(mem.LastName)));
                command.Parameters.Add(new SqlParameter("@dateOfBirth", mem.DateOfBirth));
                command.Parameters.Add(new SqlParameter("@address", Utils.SqlInjectionProtect(mem.Address)));
                command.Parameters.Add(new SqlParameter("@phone", Utils.SqlInjectionProtect(mem.Phone)));
                command.Parameters.Add(new SqlParameter("@dateAdded", mem.DateAdded));
                command.Parameters.Add(new SqlParameter("@sQuestion", Utils.SqlInjectionProtect(mem.SQuestion)));
                command.Parameters.Add(new SqlParameter("@sAnswer", Utils.SqlInjectionProtect(mem.SAnswer)));
                command.Parameters.Add(new SqlParameter("@role", mem.Role.ID));
                command.Parameters.Add(new SqlParameter("@lastLogin", mem.LastLogin));
                command.Parameters.Add(new SqlParameter("@choice", (int)choice));

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                if (choice == GlobalStorage.Action.Insert)
                    memID = (int)command.Parameters["@id"].Value;
                else
                    memID = mem.ID;

                return memID;
            }
        }

        public override ArrayList GetMembers(Member mem, int pageNum, int pageSize, string orderBy, GlobalStorage.Order order)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Members_Select", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@memID", mem.ID));
                command.Parameters.Add(new SqlParameter("@email", Utils.SqlInjectionProtect(mem.Email)));
                if (mem.Password != "")
                    command.Parameters.Add(new SqlParameter("@password", Utils.Encode(mem.Password)));
                else
                    command.Parameters.Add(new SqlParameter("@password", ""));
                command.Parameters.Add(new SqlParameter("@sex", (int)mem.Sex));
                command.Parameters.Add(new SqlParameter("@firstname", Utils.SqlInjectionProtect(mem.FirstName)));
                command.Parameters.Add(new SqlParameter("@lastname", Utils.SqlInjectionProtect(mem.LastName)));
                command.Parameters.Add(new SqlParameter("@birthdate", mem.DateOfBirth));
                command.Parameters.Add(new SqlParameter("@address", Utils.SqlInjectionProtect(mem.Address)));
                command.Parameters.Add(new SqlParameter("@phone", Utils.SqlInjectionProtect(mem.Phone)));
                command.Parameters.Add(new SqlParameter("@dateadded", mem.DateAdded));
                command.Parameters.Add(new SqlParameter("@squestion", Utils.SqlInjectionProtect(mem.SQuestion)));
                if (mem.SAnswer != "")
                    command.Parameters.Add(new SqlParameter("@sanswer", Utils.Encode(mem.SAnswer)));
                else
                    command.Parameters.Add(new SqlParameter("@sanswer", ""));
                command.Parameters.Add(new SqlParameter("@role", mem.Role.ID));
                command.Parameters.Add(new SqlParameter("@lastLogin", mem.LastLogin));
                command.Parameters.Add(new SqlParameter("@pageNumber", pageNum));
                command.Parameters.Add(new SqlParameter("@pageSize", pageSize));
                command.Parameters.Add(new SqlParameter("@orderBy", orderBy));
                command.Parameters.Add(new SqlParameter("@order", (int)order));

                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, PopulateMemberFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        public override ArrayList GetMembersSearch(Member mem, int pageNum, int pageSize, string orderBy, GlobalStorage.Order order)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Members_Select_Search", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@memID", mem.ID));
                command.Parameters.Add(new SqlParameter("@email", Utils.SqlInjectionProtect(mem.Email)));
                if (mem.Password != "")
                    command.Parameters.Add(new SqlParameter("@password", Utils.Encode(mem.Password)));
                else
                    command.Parameters.Add(new SqlParameter("@password", ""));
                command.Parameters.Add(new SqlParameter("@sex", (int)mem.Sex));
                command.Parameters.Add(new SqlParameter("@firstname", Utils.SqlInjectionProtect(mem.FirstName)));
                command.Parameters.Add(new SqlParameter("@lastname", Utils.SqlInjectionProtect(mem.LastName)));
                command.Parameters.Add(new SqlParameter("@birthdate", mem.DateOfBirth));
                command.Parameters.Add(new SqlParameter("@address", Utils.SqlInjectionProtect(mem.Address)));
                command.Parameters.Add(new SqlParameter("@phone", Utils.SqlInjectionProtect(mem.Phone)));
                command.Parameters.Add(new SqlParameter("@dateadded", mem.DateAdded));
                command.Parameters.Add(new SqlParameter("@squestion", Utils.SqlInjectionProtect(mem.SQuestion)));
                if (mem.SAnswer != "")
                    command.Parameters.Add(new SqlParameter("@sanswer", Utils.Encode(mem.SAnswer)));
                else
                    command.Parameters.Add(new SqlParameter("@sanswer", ""));
                command.Parameters.Add(new SqlParameter("@role", mem.Role.ID));
                command.Parameters.Add(new SqlParameter("@lastLogin", mem.LastLogin));
                command.Parameters.Add(new SqlParameter("@pageNumber", pageNum));
                command.Parameters.Add(new SqlParameter("@pageSize", pageSize));
                command.Parameters.Add(new SqlParameter("@orderBy", orderBy));
                command.Parameters.Add(new SqlParameter("@order", (int)order));

                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, PopulateMemberFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }
        private Member PopulateMemberFromIDataReader(IDataReader dr)
        {
            Member mem = new Member();

            mem.ID = ConvertType.ToInt(dr["member_id"]);
            mem.Email = Utils.SqlInjectionDeprotect(dr["member_email"].ToString());
            mem.Password = dr["member_password"].ToString();
            if (dr["member_sex"].ToString() == "False")
                mem.Sex = GlobalStorage.Sex.Male;
            else
                mem.Sex = GlobalStorage.Sex.Female;
            mem.FirstName = Utils.SqlInjectionDeprotect(dr["member_firstname"].ToString());
            mem.LastName = Utils.SqlInjectionDeprotect(dr["member_lastname"].ToString());
            mem.DateOfBirth = ConvertType.ToDate(dr["member_date_of_birth"]);
            mem.Address = Utils.SqlInjectionDeprotect(dr["member_address"].ToString());
            mem.Phone = Utils.SqlInjectionDeprotect(dr["member_phone"].ToString());
            mem.DateAdded = ConvertType.ToDate(dr["date_added"]);
            mem.SQuestion = Utils.SqlInjectionDeprotect(dr["member_secret_question"].ToString());
            mem.SAnswer = Utils.SqlInjectionDeprotect(dr["member_secret_answer"].ToString());
            mem.Role = Roles.GetRole(ConvertType.ToInt(dr["member_role"]), "");
            mem.LastLogin = ConvertType.ToDate(dr["member_lastlogin"]);

            return mem;
        }

        public override int CountMembers(Member mem)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Members_Count", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@memID", mem.ID));
                command.Parameters.Add(new SqlParameter("@email", Utils.SqlInjectionProtect(mem.Email)));
                if (mem.Password != "")
                    command.Parameters.Add(new SqlParameter("@password", Utils.Encode(mem.Password)));
                else
                    command.Parameters.Add(new SqlParameter("@password", ""));
                command.Parameters.Add(new SqlParameter("@sex", (int)mem.Sex));
                command.Parameters.Add(new SqlParameter("@firstname", Utils.SqlInjectionProtect(mem.FirstName)));
                command.Parameters.Add(new SqlParameter("@lastname", Utils.SqlInjectionProtect(mem.LastName)));
                command.Parameters.Add(new SqlParameter("@birthdate", mem.DateOfBirth));
                command.Parameters.Add(new SqlParameter("@address", Utils.SqlInjectionProtect(mem.Address)));
                command.Parameters.Add(new SqlParameter("@phone", Utils.SqlInjectionProtect(mem.Phone)));
                command.Parameters.Add(new SqlParameter("@dateadded", mem.DateAdded));
                command.Parameters.Add(new SqlParameter("@squestion", Utils.SqlInjectionProtect(mem.SQuestion)));
                if (mem.SAnswer != "")
                    command.Parameters.Add(new SqlParameter("@sanswer", Utils.Encode(mem.SAnswer)));
                else
                    command.Parameters.Add(new SqlParameter("@sanswer", ""));
                command.Parameters.Add(new SqlParameter("@role", mem.Role.ID));
                command.Parameters.Add(new SqlParameter("@lastLogin", mem.LastLogin));

                int count = 0;
                connection.Open();
                count = (int)command.ExecuteScalar();
                connection.Close();

                return count;
            }
        }
        public override int CountMembersSearch(Member mem)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Members_Count_Search", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@memID", mem.ID));
                command.Parameters.Add(new SqlParameter("@email", Utils.SqlInjectionProtect(mem.Email)));
                if (mem.Password != "")
                    command.Parameters.Add(new SqlParameter("@password", Utils.Encode(mem.Password)));
                else
                    command.Parameters.Add(new SqlParameter("@password", ""));
                command.Parameters.Add(new SqlParameter("@sex", (int)mem.Sex));
                command.Parameters.Add(new SqlParameter("@firstname", Utils.SqlInjectionProtect(mem.FirstName)));
                command.Parameters.Add(new SqlParameter("@lastname", Utils.SqlInjectionProtect(mem.LastName)));
                command.Parameters.Add(new SqlParameter("@birthdate", mem.DateOfBirth));
                command.Parameters.Add(new SqlParameter("@address", Utils.SqlInjectionProtect(mem.Address)));
                command.Parameters.Add(new SqlParameter("@phone", Utils.SqlInjectionProtect(mem.Phone)));
                command.Parameters.Add(new SqlParameter("@dateadded", mem.DateAdded));
                command.Parameters.Add(new SqlParameter("@squestion", Utils.SqlInjectionProtect(mem.SQuestion)));
                if (mem.SAnswer != "")
                    command.Parameters.Add(new SqlParameter("@sanswer", Utils.Encode(mem.SAnswer)));
                else
                    command.Parameters.Add(new SqlParameter("@sanswer", ""));
                command.Parameters.Add(new SqlParameter("@role", mem.Role.ID));
                command.Parameters.Add(new SqlParameter("@lastLogin", mem.LastLogin));

                int count = 0;
                connection.Open();
                count = (int)command.ExecuteScalar();
                connection.Close();

                return count;
            }
        }
        #endregion


        #region Roles Table
        public override ArrayList GetRoles(int id, string name)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Roles_Select", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@id", id));
                command.Parameters.Add(new SqlParameter("@name", Utils.SqlInjectionProtect(name)));
                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, PopulateRoleFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        private Role PopulateRoleFromIDataReader(IDataReader dr)
        {
            Role role = new Role();
            role.ID = ConvertType.ToInt(dr["role_id"]);
            role.Name = Utils.SqlInjectionDeprotect(dr["role_name"].ToString());
            return role;
        }
        #endregion


        #region ShoppingCart Table

        public override int InsertDeleteUpdateCart(Cart c, GlobalStorage.Action choice)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("ShoppingCart_InsertDeleteUpdate", connection);
                command.CommandType = CommandType.StoredProcedure;
                int cartID = -1;
                if (choice == GlobalStorage.Action.Insert)
                    command.Parameters.Add("@cID", SqlDbType.Int).Direction = ParameterDirection.Output;
                else
                    command.Parameters.Add(new SqlParameter("@cID", c.CartID));

                command.Parameters.Add(new SqlParameter("@pID", c.ProductID));
                command.Parameters.Add(new SqlParameter("@pAmount", c.ProductAmount));
                command.Parameters.Add(new SqlParameter("@mID", c.MemberID));
                command.Parameters.Add(new SqlParameter("@choice", (int)choice));

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                if (choice == GlobalStorage.Action.Insert)
                    cartID = (int)command.Parameters["@cID"].Value;
                else
                    cartID = c.CartID;

                return cartID;
            }
        }

        public override Carts GetCart(Cart c)
        {
            Carts arr = new Carts();
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("ShoppingCart_Select", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@cID", c.CartID));
                command.Parameters.Add(new SqlParameter("@pID", c.ProductID));
                command.Parameters.Add(new SqlParameter("@pAmount", c.ProductAmount));
                command.Parameters.Add(new SqlParameter("@mID", c.MemberID));
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.listCart.Insert(arr.listCart.Count, PopulateCartFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        private Cart PopulateCartFromIDataReader(IDataReader dr)
        {
            Cart cart = new Cart();
            cart.CartID = ConvertType.ToInt(dr["shopping_cart_id"]);
            cart.ProductID = ConvertType.ToInt(dr["product_id"]);
            cart.ProductAmount = ConvertType.ToInt(dr["product_amount"]);
            cart.MemberID = ConvertType.ToInt(dr["member_id"]);

            return cart;
        }

        #endregion

        #region Logs Table
        public override int InsertDeleteUpdateLog(Log l, GlobalStorage.Action choice)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("logs_InsertDeleteUpdate", connection);
                command.CommandType = CommandType.StoredProcedure;
                int logID = -1;
                if (choice == GlobalStorage.Action.Insert)
                    command.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
                else
                    command.Parameters.Add(new SqlParameter("@id", l.LogID));

                command.Parameters.Add(new SqlParameter("@ip", l.IP));
                command.Parameters.Add(new SqlParameter("@compName", l.ComputerName));
                command.Parameters.Add(new SqlParameter("@time", l.Time));
                command.Parameters.Add(new SqlParameter("@url", l.URL));
                command.Parameters.Add(new SqlParameter("@action", l.Action));
                command.Parameters.Add(new SqlParameter("@memID", l.MemberID));
                command.Parameters.Add(new SqlParameter("@choice", (int)choice));

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                if (choice == GlobalStorage.Action.Insert)
                    logID = (int)command.Parameters["@id"].Value;
                else
                    logID = l.LogID;

                return logID;
            }
        }

        public override ArrayList GetLogs(Log l, int pageNumber, int pageSize)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("logs_Select", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@id", l.LogID));
                command.Parameters.Add(new SqlParameter("@ip", Utils.SqlInjectionProtect(l.IP)));
                command.Parameters.Add(new SqlParameter("@compName", Utils.SqlInjectionProtect(l.ComputerName)));
                command.Parameters.Add(new SqlParameter("@time", l.Time));
                command.Parameters.Add(new SqlParameter("@url", Utils.SqlInjectionProtect(l.URL)));
                command.Parameters.Add(new SqlParameter("@action", Utils.SqlInjectionProtect(l.Action)));
                command.Parameters.Add(new SqlParameter("@memID", l.MemberID));
                command.Parameters.Add(new SqlParameter("@pageNumber", pageNumber));
                command.Parameters.Add(new SqlParameter("@pageSize", pageSize));
                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, PopulateLogFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        private Log PopulateLogFromIDataReader(IDataReader dr)
        {
            Log log = new Log();
            log.LogID = ConvertType.ToInt(dr["log_id"]);
            log.IP = Utils.SqlInjectionDeprotect(dr["log_ip"].ToString());
            log.ComputerName = Utils.SqlInjectionDeprotect(dr["log_computer_name"].ToString());
            log.Time = ConvertType.ToDateTime(dr["log_time"]);
            log.URL = Utils.SqlInjectionDeprotect(dr["log_url"].ToString());
            log.Action = Utils.SqlInjectionDeprotect(dr["log_action"].ToString());
            log.MemberID = ConvertType.ToInt(dr["log_member_id"]);
            return log;
        }

        public override int CountLogs(Log l)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("logs_Count", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@id", l.LogID));
                command.Parameters.Add(new SqlParameter("@ip", Utils.SqlInjectionProtect(l.IP)));
                command.Parameters.Add(new SqlParameter("@compName", Utils.SqlInjectionProtect(l.ComputerName)));
                command.Parameters.Add(new SqlParameter("@time", l.Time));
                command.Parameters.Add(new SqlParameter("@url", Utils.SqlInjectionProtect(l.URL)));
                command.Parameters.Add(new SqlParameter("@action", Utils.SqlInjectionProtect(l.Action)));
                command.Parameters.Add(new SqlParameter("@memID", l.MemberID));

                int count = 0;
                connection.Open();
                count = (int)command.ExecuteScalar();
                connection.Close();

                return count;
            }
        }
        #endregion


        #region Orders Table
        public override int InsertDeleteUpdateOrder(Order o, GlobalStorage.Action choice)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Orders_InsertDeleteUpdate", connection);
                command.CommandType = CommandType.StoredProcedure;
                int orderID = -1;
                if (choice == GlobalStorage.Action.Insert)
                    command.Parameters.Add("@orderID", SqlDbType.Int).Direction = ParameterDirection.Output;
                else
                    command.Parameters.Add(new SqlParameter("@orderID", o.OrderID));

                command.Parameters.Add(new SqlParameter("@sCartID", o.ShoppingCartID));
                command.Parameters.Add(new SqlParameter("@state", o.State));
                command.Parameters.Add(new SqlParameter("@dateAdded", o.DateAdded));
                command.Parameters.Add(new SqlParameter("@choice", (int)choice));

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                if (choice == GlobalStorage.Action.Insert)
                    orderID = (int)command.Parameters["@orderID"].Value;
                else
                    orderID = o.OrderID;

                return orderID;
            }
        }

        public override ArrayList GetOrders(Order o, int pageNumber, int pageSize, int memID)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Orders_Select", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@orderID", o.OrderID));
                command.Parameters.Add(new SqlParameter("@sCartID", o.ShoppingCartID));
                command.Parameters.Add(new SqlParameter("@state", (int)o.State));
                command.Parameters.Add(new SqlParameter("@dateAdded", o.DateAdded));
                command.Parameters.Add(new SqlParameter("@memID", memID));
                command.Parameters.Add(new SqlParameter("@pageNumber", pageNumber));
                command.Parameters.Add(new SqlParameter("@pageSize", pageSize));
                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, PopulateOrderFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }

        private Order PopulateOrderFromIDataReader(IDataReader dr)
        {
            Order o = new Order();

            o.OrderID = ConvertType.ToInt(dr["order_id"]);
            o.ShoppingCartID = ConvertType.ToInt(dr["shopping_cart_id"]);
            switch (dr["state"].ToString())
            {
                case "0":
                    o.State = GlobalStorage.State.Order;
                    break;
                case "1":
                    o.State = GlobalStorage.State.Shipping;
                    break;
                case "2":
                    o.State = GlobalStorage.State.Complete;
                    break;
            }
            o.DateAdded = ConvertType.ToDate(dr["date_added"]);

            return o;
        }

        public override int CountOrders(Order o)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Orders_Count", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@orderID", o.OrderID));
                command.Parameters.Add(new SqlParameter("@sCartID", o.ShoppingCartID));
                command.Parameters.Add(new SqlParameter("@state", o.State));
                command.Parameters.Add(new SqlParameter("@dateAdded", o.DateAdded));

                int count = 0;
                connection.Open();
                count = (int)command.ExecuteScalar();
                connection.Close();

                return count;
            }
        }

        public override int CountOrdersByMemberID(int memberID)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Orders_CountByMemberID", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@memID", memberID));

                int count = 0;
                connection.Open();
                count = (int)command.ExecuteScalar();
                connection.Close();

                return count;
            }
        }
        #endregion

        #region Reviews Table
        public override Review GetReviewsByID(int reviewID)
        {
            Review rev = new Review();
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Reviews_SelectByID", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@rID", reviewID));
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    rev = ReviewFromIDataReader(reader);
                }
                connection.Close();
                return rev;
            }
        }
        public override ArrayList GetReviews(int productID)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Reviews_SelectByProduct", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@pID", productID));
                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, ReviewFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }
        public override ArrayList GetReviews(int productID, int pageNumber, int pageSize)
        {
            ArrayList kq = new ArrayList();
            ArrayList revs = GetReviews(productID);
            int count = revs.Count;
            int start = (pageNumber - 1) * pageSize;
            int end = start;
            if (start < 0)
                return null;
            else
            {
                if (start + pageSize - 1 >= count)//het
                    end = count - 1;
                else
                    end = start + pageSize - 1;
                for (int i = start; i <= end; i++)
                {
                    kq.Add(revs[i]);
                }
            }
            return kq;
        }
        public Review ReviewFromIDataReader(SqlDataReader dr)
        {
            Review rev = new Review();
            rev.RID = ConvertType.ToInt(dr["review_id"]);
            rev.RTitle = Utils.SqlInjectionDeprotect(dr["review_title"].ToString());
            rev.RContent = Utils.SqlInjectionDeprotect(dr["review_content"].ToString());
            rev.PID = ConvertType.ToInt(dr["product_id"]);
            rev.Dateadded = ConvertType.ToDate(dr["date_added"]);
            rev.MID = ConvertType.ToInt(dr["member_id"]);
            return rev;
        }
        public override int CreateUpdateDeleteReview(Review rev, GlobalStorage.Action choice)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Reviews_InsertDeleteUpdate", connection);
                command.CommandType = CommandType.StoredProcedure;
                int revID = -1;
                if (choice == GlobalStorage.Action.Insert)
                    command.Parameters.Add("@rID", SqlDbType.Int).Direction = ParameterDirection.Output;
                else
                    command.Parameters.Add(new SqlParameter("@rID", rev.RID));
                command.Parameters.Add(new SqlParameter("@rTitle", Utils.SqlInjectionProtect(rev.RTitle)));
                command.Parameters.Add(new SqlParameter("@rContent", Utils.SqlInjectionProtect(rev.RContent)));
                command.Parameters.Add(new SqlParameter("@pID", rev.PID));
                command.Parameters.Add(new SqlParameter("@dateadded", rev.Dateadded));
                command.Parameters.Add(new SqlParameter("@mID", rev.MID));
                command.Parameters.Add(new SqlParameter("@choice", (int)choice));

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                if (choice == GlobalStorage.Action.Insert)
                    revID = (int)command.Parameters["@rID"].Value;
                else
                    revID = rev.MID;

                return revID;
            }
        }
        #endregion Reviews Table

        #region

        public override ArrayList GetAllArticle(int id, string title, string type, int lang)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("Article_Select", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@id", id));
                command.Parameters.Add(new SqlParameter("@art_title", title));
                command.Parameters.Add(new SqlParameter("@art_type", type));
                command.Parameters.Add(new SqlParameter("@language", lang));
                ArrayList arr = new ArrayList();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        arr.Insert(arr.Count, ArticleFromIDataReader(reader));
                    }
                    reader.Close();
                    connection.Close();
                }
                return arr;
            }
        }
        public static Article ArticleFromIDataReader(SqlDataReader reader)
        {
            Article rev = new Article();
            rev.Id = ConvertType.ToInt(reader["id"]);
            rev.Title = Utils.SqlInjectionDeprotect(reader["art_title"].ToString());
            rev.Content = Utils.SqlInjectionDeprotect(reader["art_content"].ToString());
            rev.Type = Utils.SqlInjectionDeprotect(reader["art_type"].ToString());
            rev.Top = ConvertType.ToBool(reader["art_top"].ToString());
            rev.Active = ConvertType.ToBool(reader["active"]);
            rev.Sequence = ConvertType.ToInt(reader["sequence"]);
            rev.Image = Utils.SqlInjectionDeprotect(reader["art_image"].ToString());
            return rev;
        }
        public override int CreateUpdateDeleteArticle(Article rev, ShopContent shop, GlobalStorage.Action choice)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand("article_InsertDeleteUpdate", connection);
                command.CommandType = CommandType.StoredProcedure;
                int revID = -1;
                if (choice == GlobalStorage.Action.Insert)
                    command.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
                else
                    command.Parameters.Add(new SqlParameter("@id", rev.Id));
                command.Parameters.Add(new SqlParameter("@art_title", Utils.SqlInjectionProtect(shop.NameVi)));
                command.Parameters.Add(new SqlParameter("@art_title_en", Utils.SqlInjectionProtect(shop.NameEn)));
                command.Parameters.Add(new SqlParameter("@art_type", Utils.SqlInjectionProtect(rev.Type)));

                command.Parameters.Add(new SqlParameter("@art_content", Utils.SqlInjectionProtect(shop.DesVi)));
                command.Parameters.Add(new SqlParameter("@art_content_en", Utils.SqlInjectionProtect(shop.DesEn)));


                command.Parameters.Add(new SqlParameter("@art_active", rev.Active));
                command.Parameters.Add(new SqlParameter("@art_top", rev.Top));
                command.Parameters.Add(new SqlParameter("@sequence", rev.Sequence));
                command.Parameters.Add(new SqlParameter("@image", Utils.SqlInjectionProtect(rev.Image)));
                command.Parameters.Add(new SqlParameter("@choice", (int)choice));
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                if (choice == GlobalStorage.Action.Insert)
                    revID = (int)command.Parameters["@id"].Value;
                else
                    revID = rev.Id;

                return revID;
            }
        }
        #endregion Table Article
    }
}